CREATE DATABASE CourierDatabase
GO
USE CourierDatabase
GO
CREATE TABLE TempTable
(
	IDEmp INT,
	IDCourier INT,
	NowDate DATE
)

CREATE TABLE Secure_Question
(
	Q_Id INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
	Content VARCHAR(70) NOT NULL
)

CREATE TABLE Emp_Decentralization
(
	Decentralization_Id INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
	Decentralization_Name VARCHAR(25) NOT NULL
)

CREATE TABLE Working_Status
(
	Status_Id INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
	Status_Content VARCHAR(20)
)
CREATE TABLE Employee
(
	Emp_Id INT NOT NULL,
	Emp_Name VARCHAR(25) NOT NULL,
	Emp_Address VARCHAR(40) NOT NULL,
	Emp_Phone INT NOT NULL,
	Emp_Mail VARCHAR(40) NOT NULL,
	Emp_Image VARCHAR(100) NULL,
	Status_Id INT NOT NULL FOREIGN KEY REFERENCES Working_Status(Status_Id),
	Decentralization_Id INT NOT NULL FOREIGN KEY REFERENCES Emp_Decentralization (Decentralization_Id),
	Login_Name VARCHAR(15) NOT NULL,
	Login_Password VARCHAR(50) NOT NULL,
	S_Question INT NOT NULL FOREIGN KEY REFERENCES Secure_Question(Q_Id),
	S_Answer VARCHAR(40),
	Time_Created DATE NOT NULL,
	PRIMARY KEY(Login_Name)
)

CREATE TABLE Attendance
(
	Attendance_Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Login_Name VARCHAR(15) NOT NULL FOREIGN KEY REFERENCES Employee(Login_Name),
	Absence INT NULL,
	Absence_Date DATE NULL,
	Until_Day DATE NULL,
	Note VARCHAR(100)NULL,
	Before_Status INT FOREIGN KEY REFERENCES Emp_Decentralization(Decentralization_Id)
)
CREATE TABLE Lost_Connection
(
	Connection_Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Login_Name VARCHAR(15) NOT NULL FOREIGN KEY REFERENCES Employee(Login_Name),
	Connected INT
)
---------------------

CREATE TABLE Point
(
	Point_Id INT IDENTITY(0,1) PRIMARY KEY,
	Point_Name VARCHAR(50)
)
CREATE TABLE Road
(
	Road_Id INT IDENTITY(0,1) PRIMARY KEY,
	Point_A INT NOT NULL FOREIGN KEY REFERENCES Point(Point_Id),
	Point_B INT NOT NULL FOREIGN KEY REFERENCES Point(Point_Id),
	Distance INT
)


CREATE TABLE Courier_Details
(
	Courier_Id INT IDENTITY(1,1),
	Courier_Name VARCHAR(15) NOT NULL PRIMARY KEY,
	Time_Created DATE ,
	Login_Name VARCHAR(15) NOT NULL FOREIGN KEY REFERENCES Employee (Login_Name),
	Sender_Name VARCHAR(15),
	Sender_Address VARCHAR(20),
	Sender_Phone INT,
	Receiver_Name VARCHAR(15),
	Receiver_Address VARCHAR(20),
	Receiver_Phone INT,
	Courier_Status INT NOT NUll FOREIGN KEY REFERENCES Point(Point_Id),
	Amount FLOAT,
	Weight_ FLOAT,
	SenderProvince INT NOT NULL FOREIGN KEY REFERENCES Point(Point_Id),
	ReceiverProvince INT NOT NULL FOREIGN KEY REFERENCES Point(Point_Id)
)

CREATE TABLE Charges
(
	Distance INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Price FLOAT NOT NULL,
	Addition FLOAT NOT NULL
)


